package com.waimai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.waimai.adapter.RestaurantAdapter;
import com.waimai.constant.DaoConstant;
import com.waimai.controllor.BaseControllor;
import com.waimai.entity.Address;
import com.waimai.entity.Restaurant;
import com.waimai.entity.User;
import com.waimai.log.Log;
import com.waimai.utils.StringUtils;

public class DBDao extends BaseControllor {
	DBManager manager = new DBManager();
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	private void open(String sql) throws SQLException {
		conn = manager.getConnection();
		ps = conn.prepareStatement(sql);
	}

	private void close() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				Log.e(e, "sql resutset close error");
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				Log.e(e, "sql statement close error");
				e.printStackTrace();
			} finally {
				ps = null;
			}
		}
		manager.releaseConnection();
	}

	/**
	 * 
	 * @param
	 * @return
	 */
	public boolean addUser(User u) {
		boolean result = true;
		String sql = "insert into " + DaoConstant.USER_TABLE
				+ " (deciveId,userId) " + " values(?,?);";
		try {
			open(sql);
			ps.setString(1, u.getDeviceId());
			ps.setString(2, u.getUserId());
			ps.executeUpdate();
		} catch (SQLException e) {
			Log.e(e, "sql addUser is error\n sql = " + sql);
			e.printStackTrace();
			result = false;
		} finally {
			close();
		}
		return result;
	}

	/**
	 * 
	 * @param adapter
	 * @return
	 */
	public boolean addRA_Relationship(RestaurantAdapter adapter) {
		boolean result = false;
		String sql = "insert into " + DaoConstant.RA_RELATIONSHIP_TABLE
				+ " (rId, aId, SendTime, StartPrice, SendPrice,id) "
				+ " values(?,?,?,?,?,?);";
		try {
			open(sql);
			ps.setString(1, adapter.getId());
			ps.setString(2, adapter.getAddress().getId());
			ps.setString(3, adapter.getSendTime());
			ps.setString(4, adapter.getStartPrice());
			ps.setString(5, adapter.getSendPrice());
			ps.setString(6, StringUtils.getUUID());
			ps.executeUpdate();
		} catch (SQLException e) {
			Log.e(e, "sql addRA_Relationship is error\n sql = " + sql);
			e.printStackTrace();

		} catch (NullPointerException e) {
			Log.e(e, "sql addRA_Relationship is error\n sql = " + sql);
			e.printStackTrace();

		} finally {
			close();
		}
		return result;
	}

	/**
	 * id:是company+href
	 * 
	 * @param r
	 * @return
	 */
	public boolean addRestaurant(Restaurant r) {
		boolean result = true;
		String sql = "insert into "
				+ DaoConstant.RESTAURANT_TABLE
				+ " (NAME, salelist, HrefUrl, DrinkList, Company, MaxBack, STATUS, Imgurl, TotalPerMon, MinusRatio,id) "
				+ " values(?,?,?,?,?,?,?,?,?,?,?);";
		try {
			open(sql);
			ps.setString(1, r.getName());
			ps.setString(2, r.getSaleString());
			ps.setString(3, r.getHrefUrl());
			ps.setString(4, r.getDrinkString());
			ps.setInt(5, r.getCompany());
			ps.setInt(6, r.getMaxBack());
			ps.setInt(7, r.getStatus());
			ps.setString(8, r.getImgUrl());
			ps.setString(9, r.getTotalPerMon());
			ps.setDouble(10, r.getMinusRatio());
			ps.setString(11, r.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			String error = "sql addRestaurant is error \n sql = " + sql;
			if (r != null) {
				error = error + r.getName();
			}
			Log.e(e, error);
			result = false;
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(e, "");
			e.printStackTrace();

		} finally {
			close();
		}
		// after("add restaurant : href is " + r.getHrefUrl());
		return result;
	}

	public int updateRA_Relationship(RestaurantAdapter adapter) {
		int result = 0;
		String sql = "update " + DaoConstant.RA_RELATIONSHIP_TABLE
				+ " set SendTime = ? , StartPrice = ? , SendPrice = ? "
				+ " where rId = ? and aId = ? ;";
		try {
			open(sql);
			ps.setString(1, adapter.getSendTime());
			ps.setString(2, adapter.getStartPrice());
			ps.setString(3, adapter.getSendPrice());
			ps.setString(4, adapter.getId());
			ps.setString(5, adapter.getAddress().getId());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			String error = "sql updateRelationship is error \n sql = " + sql;
			if (adapter != null) {
				error = error + adapter.getName();
			}
			Log.e(e, error);
			e.printStackTrace();
		} finally {
			close();
		}
		return result;
	}

	// restautant id 是 company+href
	public int updateRestaurant(Restaurant r) {
		int result = 0;
		String sql = "update "
				+ DaoConstant.RESTAURANT_TABLE
				+ " set NAME = ? , salelist = ? , DrinkList = ? , Company = ? , MaxBack = ? , STATUS = ? , TotalPerMon = ? , MinusRatio = ? "
				+ "where id = ? ;";
		try {
			open(sql);
			ps.setString(1, r.getName());
			ps.setString(2, r.getSaleString());
			ps.setString(3, r.getDrinkString());
			ps.setInt(4, r.getCompany());
			ps.setInt(5, r.getMaxBack());
			ps.setInt(6, r.getStatus());
			ps.setString(7, r.getTotalPerMon());
			ps.setDouble(8, r.getMinusRatio());
			ps.setString(9, r.getCompany() + r.getHrefUrl());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			String error = "sql updateRestaurant is error \n sql = " + sql;
			if (r != null) {
				error = error + r.getName();
			}
			Log.e(e, error);
			e.printStackTrace();
		} finally {
			close();
		}
		return result;
	}

	public List<Address> selectAddress() {
		List<Address> addressList = new ArrayList<Address>();
		String sql = "select * from " + DaoConstant.ADDRESS_TABLE;

		try {
			open(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				addressList.add(new Address(rs.getString(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5), rs
								.getString(6), rs.getString(7)));
			}
		} catch (SQLException e) {
			Log.e(e, "sql selectAddress is error\n sql = " + sql);
			e.printStackTrace();
		} finally {
			close();
		}
		return addressList;
	}

	public boolean clearTable(String tableName) {

		boolean result = true;
		String sql = "delete from " + tableName + " where 1 = 1";
		try {
			open(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			result = false;
			Log.e(e, "sql clear table " + tableName + " error\n sql = " + sql);
			e.printStackTrace();
		} finally {
			close();
		}
		return result;
	}
}
